package main.staff;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import main.util.ArrayUtil;
import main.util.StringUtil;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;



public class CreateTypesSql {
	
	public static Map<String,Integer> address;
	
	public void setLocation(String name,int id){
		address.put(name, id);
	}

	public static void topWrite(FileWriter fw){
		if(address==null){
			address=new HashMap<String, Integer>();
			address.put("staffId", 4);
			address.put("orgaId", 8);
			address.put("staffName", 5);
			address.put("mobilePhone", 6);
			address.put("roleId", 10);
			address.put("serviceTypeId", 3);
		}
		
		try {
			fw.write("/*\n");
			//fw.write("Host           : 192.168.111.45:23307\n");
			fw.write("Host           : 192.168.123.244:20001\n");
			fw.write("Database       : ngmtt\n\n\n");
			fw.write("Author:  王兴飞\n");
			fw.write("Tel:  15637909515\n");
			fw.write("Date: "+new Date()+"\n");
			fw.write("*/\n");
			
			fw.write("use ngmtt;\n");
			fw.write("set names utf8;\n");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static String staffbasicinfo(Row row,String staffid){
		String sql=null;
		String orgaId=removeNull(row,address.get("orgaId"));
		sql="INSERT INTO t_ucp_staffbasicinfo (`staffid`, `STAFFNAME`, `STAFFSTATE`, `STAFFIDSTATUS`, `DLEVELID`, `SECONDPOST`, `DUTYID`, `SECONDDUTY`, `orgaid`, `POSTID`, `INSERTTIME`, `joinDate`, `MOBILEPHONE`, `EMAILADDRESS`, `CITYID`, `ProviceID`, `STAFF_TYPE`, `ID_CARD_NO`, `CHANNEL_ID`, `TENANT_ID`) VALUES "
				+ "('"+removeNull(row,address.get("staffId"))+"', '"+removeNull(row,address.get("staffName"))+"', '1', '01', '002', '', '002', '', '"
				+orgaId+"', '002', NOW(), '2015:07:15 00:00:00', '"+removeNull(row,address.get("mobilePhone"))+"', '', '', '"+orgaId.substring(0, 8)+"', '', '', '', '001')";
		return sql+";\n";
	}
	
	public static String staffbasicinfo2(Row row,String staffid,String orgaId){
		String sql=null;
		sql="INSERT INTO t_ucp_staffbasicinfo (`staffid`, `STAFFNAME`, `STAFFSTATE`, `STAFFIDSTATUS`, `DLEVELID`, `SECONDPOST`, `DUTYID`, `SECONDDUTY`, `orgaid`, `POSTID`, `INSERTTIME`, `joinDate`, `MOBILEPHONE`, `EMAILADDRESS`, `CITYID`, `ProviceID`, `STAFF_TYPE`, `ID_CARD_NO`, `CHANNEL_ID`, `TENANT_ID`,`authen_rule_id`) VALUES "
				+ "('"+removeNull(row,address.get("staffId"))+"', '"+removeNull(row,address.get("staffName"))+"', '1', '01', '002', '', '002', '', '"
				+orgaId+"', '002', NOW(), '2015:07:15 00:00:00', '"+removeNull(row,address.get("mobilePhone"))+"', '', '', '"+orgaId.substring(0, 8)+"', '', '', '', '001','2')";
		return sql+";\n";
	}
	
	public static String staffbasicinfo(Row row,String staffid,String orgaId){
		String sql=null;
		//String orgaId=removeNull(row,address.get("orgaId"));
		sql="INSERT INTO t_ucp_staffbasicinfo (`staffid`, `STAFFNAME`, `STAFFSTATE`, `STAFFIDSTATUS`, `DLEVELID`, `SECONDPOST`, `DUTYID`, `SECONDDUTY`, `orgaid`, `POSTID`, `INSERTTIME`, `joinDate`, `MOBILEPHONE`, `EMAILADDRESS`, `CITYID`, `ProviceID`, `STAFF_TYPE`, `ID_CARD_NO`, `CHANNEL_ID`, `TENANT_ID`) VALUES "
				+ "('"+removeNull(row,address.get("staffId"))+"', '"+removeNull(row,address.get("staffName"))+"', '1', '01', '002', '', '002', '', '"
				+orgaId+"', '002', NOW(), '2015:07:15 00:00:00', '"+removeNull(row,address.get("mobilePhone"))+"', ";
		if(address.get("email")!=null){
			String email=removeNull(row,address.get("email")).trim();
			if(!StringUtil.isEmpty(email)){
				sql=sql+"'"+email+"'";
			}else{
				sql=sql+"''";
			}
		}
		
		return sql+", '', '"+orgaId.substring(0, 8)+"', '', '', '', '001')"+";\n";
	}
	
	public static String ormSql(Row row,String staffid){
		String sql=null;
		String origin=removeNull(row,1);
		String orgaId=removeNull(row,address.get("orgaId"));
		sql="INSERT INTO `t_ucp_staff_orm_kf` (`STAFF_ID`, `PROV_ID`, `SYS_NO`, `ORIG_STAFF_ID`, `CRT_TIME`, `UPDATE_TIME`) "
				+ "VALUES ('"+staffid+"', '"+orgaId.substring(0, 8)+"', 'CRM', '"+origin+"', '2017-04-11 14:31:29', NULL)";
		return sql+";\n";
	}
	
	public static String ormSql(Row row,String staffid,String sysName,String orgaId){
		String sql=null;
		if(address.get(sysName)==null){
			return "";
		}
		
		String origin=removeNull(row,address.get(sysName));
		if(StringUtil.isEmpty(origin)){
			return "";
		}
		
		sql="INSERT INTO `t_ucp_staff_orm_kf` (`STAFF_ID`, `PROV_ID`, `SYS_NO`, `ORIG_STAFF_ID`, `CRT_TIME`, `UPDATE_TIME`) "
				+ "VALUES ('"+staffid+"', '"+orgaId.substring(0, 8)+"', '"+sysName+"', '"+origin+"', '2017-04-11 14:31:29', NULL);";
		return sql+";\n";
	}
	
	public static String staffrole(Row row,String staffid){
		String sql=null;
		sql="INSERT INTO t_ucp_staffrole (`RoleId`, `staffid`) VALUES ";
		
		String[] roles=removeNull(row, address.get("roleId")).split(",");
		//可能有多个角色
		for(int i=0;i<roles.length;i++){
			roles[i]="('"+roles[i]+"', '"+staffid+"')";
		}
		
		return sql+ArrayUtil.conectArray(roles, ",")+";\n";
	}
	public static String staffserviceType(Row row,String staffid){
		String sql=null;
		sql="INSERT INTO t_ucp_staffservicetype (`servicetypeId`, `staffid`) VALUES ";
		
		if(address.get("serviceTypeId")==null){
			return "";
		}
		
		String[] roles=removeNull(row, address.get("serviceTypeId")).split(",");
		//可能有多个角色
		for(int i=0;i<roles.length;i++){
			roles[i]="('"+roles[i]+"', '"+staffid+"')";
		}
		
		return sql+ArrayUtil.conectArray(roles, ",")+";\n";
	}
	public static String password(String staffId){
		String sql=null;
		Map<String,String> pass=PasswordHelper.encryptPassword("Zyzx@2016", staffId, null);
 		sql="INSERT INTO t_ucp_password (STAFFID,PASSWORD,SALT,BEGINDATE,ENDDATE,UPDATEDATE) VALUES ("
 				+ "'"+staffId+"','"+pass.get("password")+"','"+pass.get("salt")+"',NULL,'2019-12-08 15:35:58','2015-12-08 16:58:30');";
		return sql+"\n";
	}
	
	public static void main(String[] args) {
		System.out.println(password("XT00002"));
		//System.out.println(password("huyue"));
	}
	
	/*去掉字符串为空格的值*/
	public static String removeNull(Row row,int id){
		Cell cell=row.getCell(id);
		
		if(cell==null){
			return "";
		}
		
		if(CellType.NUMERIC.equals(cell.getCellTypeEnum())){
			Double d=cell.getNumericCellValue();
			return new DecimalFormat("#").format(d);
		}
		return cell.toString().replaceAll("\\s", "");
	}
	
	public static void sortCol(Sheet sheet){
		int rowNum=sheet.getLastRowNum()+1;
		//int colNum=sheet.getl
		
		
		/*for(int i=0;i<sheet.getLastRowNum()+1;i++){
			Row row = sheet.getRow(i);
			String staffid=CreateTypesSql.removeNull(row, 4);
			
			if(staffid.matches("[a-zA-Z]{2}?[0-9]{5}?")){
				staffInfo.write(CreateTypesSql.staffbasicinfo(row,staffid));
				role.write(CreateTypesSql.staffrole(row,staffid));
				serviceType.write(CreateTypesSql.staffserviceType(row,staffid));
				password.write(CreateTypesSql.password(staffid));
				System.out.println(staffid+":成功   "+i);
			}else{
				System.out.println(staffid+":loss  "+i);
			}
		}*/
	}

	public static void topWriteEnd(FileWriter fw) {
		// TODO Auto-generated method stub
		try {
			fw.write("commit;\n");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
